﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace WaveMaster.Holidays.Club.SQL
{
    public class BookingSQL
    {
        public static string GetAllBooking()
        {
            string sql = "select * from booking order by m_cretnDate DESC LIMIT 100";
            return sql;
        }

        public static string GetAllBookingByStatus()
        {
            string sql = "select * from booking where m_bookingStatus=?m_bookingStatus order by m_cretnDate DESC LIMIT 100";
            return sql;
        }

        public static string GetAllBookingByTravelAgent()
        {
            string sql = "select * from booking where m_travelAgentId=?m_travelAgentId order by m_cretnDate DESC   LIMIT 100";
            return sql;
        }

        public static string GetTotalBookingByTravelAgent()
        {
            string sql = "select sum(m_bookNumber) from booking where m_travelAgentId=?m_travelAgentId order by m_bookingDate DESC";
            return sql;
        }

        public static string GetBookingByID()
        {
            string sql = "select * from booking where m_bookingID = ?m_bookingID ";
            return sql;
        }
        public static string GetBookingHistoryByBookingID()
        {
            string sql = "select * from booking_history where m_bookingID = ?m_bookingID order by m_bookinghistoryid desc limit 1";
            return sql;
        }

        public static string GetPassengerHistoryByPassengerID()
        {
            string sql = "select * from passanger_history where m_passangerid = ?m_passangerid order by m_passengerhistoryid desc limit 1";
            return sql;
        }
        
        public static string SearchBookingByCodeName(string p)
        {
            string sql = "select * from booking WHERE concat(m_groupName,m_bookingCode) like '%" + p + "%'";
            return sql;
        }

        public static string SearchBookingByDepartureDate()
        {
            string sql = "select * from booking WHERE date(b.m_departuredate) = ?m_departuredate";
            return sql;
        }

        public static string SearchBookingByCodeNameDepartureDate(string p)
        {
            string sql = "select * from booking WHERE date(m_departuredate) = ?m_departuredate and concat(m_groupName,m_bookingCode) like '%" + p + "%'";
            return sql;
        }

        public static string SearchBookingByCodeNameTripDepartureDate(string p)
        {
            string sql = @"select * from booking b, trip t 
            WHERE b.m_tripid = t.m_tripid and date(t.m_departuredate) = ?m_departuredate and concat(b.m_groupName,b.m_bookingCode) like '%" + p + "%' order by t.m_departureTime asc";
            return sql;
        }

        public static string SearchCheckInByTrip()
        {
            string sql = @"select * from booking WHERE m_tripid = ?m_tripid";
            return sql;
        }

        public static string SearchBookingByCodeNameDepartureDateStatus(string p)
        {
            string sql = "select * from booking WHERE m_bookingStatus=?m_bookingStatus and date(m_departuredate) = ?m_departuredate and concat(m_groupName,m_bookingCode) like '%" + p + "%'";
            return sql;
        }

        public static string SearchBookingByCodeNameAndTravelAgentId(string p)
        {
            string sql = "select * from booking WHERE concat(m_groupName,m_bookingCode) like '%" + p + "%' and m_travelAgentId=?m_travelAgentId";
            return sql;
        }

        public static string SearchBookingByCodeNameAndTravelAgentIdAndDatetimeStatus(string p)
        {
            string sql = "select * from booking WHERE m_bookingStatus=?m_bookingStatus and concat(m_groupName,m_bookingCode) like '%" + p + "%' and m_travelAgentId=?m_travelAgentId and date(m_departuredate) = ?m_departuredate";
            return sql;
        }

        public static string SearchBookingByCodeNameAndTravelAgentIdAndDatetime(string p)
        {
            string sql = "select * from booking WHERE concat(m_groupName,m_bookingCode) like '%" + p + "%' and m_travelAgentId=?m_travelAgentId and date(m_departuredate) = ?m_departuredate";
            return sql;
        }

        public static string DeleteBookingByID()
        {
            return @"DELETE FROM booking
      WHERE m_bookingID = ?m_bookingID";
        }
        public static string InsertBooking()
        {
            string sql = @"INSERT INTO booking
           (m_groupName
           ,m_bookNumber
           ,m_tripID
            ,m_remark
            ,m_travelAgentId
            ,m_bookingCode
            ,m_ticketType
            ,m_cretnDate            
            ,m_cretnBy
            ,m_modfctnDate
            ,m_modfctnBy
            ,m_bookingDate
            ,m_lastBalance
            ,m_bookingStatus
            ,m_returnDate
            ,m_departureDate
            ,m_returntrip
            )
     VALUES
           (?m_groupName
           ,?m_bookNumber
           ,?m_tripID
            ,?m_remark
            ,?m_travelAgentId
             ,?m_bookingCode
              ,?m_ticketType
             ,?m_cretnDate
            , ?m_cretnBy
            , ?m_modfctnDate
            ,?m_modfctnBy
            ,?m_bookingDate
            ,?m_lastBalance
            ,?m_bookingStatus
            ,?m_returnDate
            ,?m_departureDate
            ,?m_returntrip
          )";
            return sql;
        }
        public static string InsertBookingHistory()
        {
            string sql = @"INSERT INTO booking_history
           (m_groupName
           ,m_bookNumber
           ,m_tripID
            ,m_remark
            ,m_travelAgentId
            ,m_bookingCode
            ,m_ticketType
            ,m_cretnDate            
            ,m_cretnBy
            ,m_modfctnDate
            ,m_modfctnBy
            ,m_bookingDate
            ,m_lastBalance
            ,m_bookingStatus
            ,m_returnDate
            ,m_departureDate
            ,m_bookingID
            ,m_returntrip
            )
     VALUES
           (?m_groupName
           ,?m_bookNumber
           ,?m_tripID
            ,?m_remark
            ,?m_travelAgentId
             ,?m_bookingCode
              ,?m_ticketType
             ,?m_cretnDate
            , ?m_cretnBy
            , ?m_modfctnDate
            ,?m_modfctnBy
            ,?m_bookingDate
            ,?m_lastBalance
            ,?m_bookingStatus
            ,?m_returnDate
            ,?m_departureDate
            ,?m_bookingID
            ,?m_returntrip
          )";
            return sql;
        }
        public static string UpdateBooking()
        {
            string sql = @"UPDATE booking
   SET m_groupName=?m_groupName
           ,m_bookNumber=?m_bookNumber
           ,m_tripID=?m_tripID
            ,m_remark=?m_remark
            ,m_travelAgentId=?m_travelAgentId
            ,m_bookingCode =?m_bookingCode
            ,m_ticketType=?m_ticketType
            ,m_modfctnDate=?m_modfctnDate
            ,m_modfctnBy=?m_modfctnBy
            ,m_bookingDate=?m_bookingDate
            ,m_lastBalance=?m_lastBalance
            ,m_bookingStatus=?m_bookingStatus
            ,m_returnDate=?m_returnDate
            ,m_departureDate=?m_departureDate
 WHERE m_bookingID = ?m_bookingID";
            return sql;
        }

        public static string UpdateBookingStatus()
        {
            string sql = @"UPDATE booking
   SET m_bookingStatus=?m_bookingStatus
 WHERE m_bookingID = ?m_bookingID";
            return sql;
        }

        public static string GetMaxBookingID()
        {
            return @"SELECT MAX(m_bookingID) FROM booking";
        }

        public static string  InsertBookStockByTravelAgent()
        {
            return @"INSERT INTO book_stock
           (m_travelAgentId, OneWay, TwoWay) VALUES
           (?m_travelAgentId,0,0)";
        }

        public static string UpdateAddOneWayBookStock()
        {
            return @"UPDATE book_stock
   SET OneWay=OneWay+?addition WHERE m_travelAgentId=?m_travelAgentId";
        }

        public static string UpdateAddTwoWayBookStock()
        {
            return @"UPDATE book_stock
   SET TwoWay=TwoWay+?addition WHERE m_travelAgentId=?m_travelAgentId";
        }

        public static string UpdateSubstractOneWayBookStock()
        {
            return @"UPDATE book_stock
   SET OneWay=OneWay-?substract WHERE m_travelAgentId=?m_travelAgentId";
        }

        public static string UpdateSubstractTwoWayBookStock()
        {
            return @"UPDATE book_stock
   SET TwoWay=TwoWay-?substract WHERE m_travelAgentId=?m_travelAgentId";
        }

        public static string GetOneWayBookStock()
        {
            return @"SELECT OneWay FROM book_stock WHERE m_travelAgentId=?m_travelAgentId";
        }

        public static string GetTwoWayBookStock()
        {
            return @"SELECT TwoWay FROM book_stock WHERE m_travelAgentId=?m_travelAgentId";
        }

        public static string ReportItineraryBookingByID()
        {
            string sql = @"select *, ta.m_name as travelagentname from passanger p, booking b, trip t, vessel v, seaport s, passport h,
                            travel_agent ta
                            where
                            p.m_bookingID = b.m_bookingID
                            and t.m_tripID = b.m_tripID
                            and v.m_vesselID = t.m_vesselID
                            and s.m_seaPortID = t.m_seaPortID
                            and h.m_passportID = p.m_passportID
                            and ta.m_travelAgentId = b.m_travelAgentId
                            and b.m_bookingID = ?m_bookingID";
            return sql;
        }
        public static string ReportItineraryBookingPassengerByID()
        {
            string sql = @"select *, ta.m_name as travelagentname,
                            h.m_name as passname,
                            h.m_passportNo as passportcd,
                            h.m_officeissue as passofficeissue,
                            h.m_expiredate as passexpireddate,
                            h.m_country as countryname,
                            h.m_birthDate as passdateofbirth,
                            h.m_birthPlace as passplaceofbirth,
                            m_vesselName as vesselcd,
                            h.m_gender as passgender
                            from passanger p, booking b, trip t, vessel v, seaport s, passport h,
                            travel_agent ta, seaport d 
                            where
                            p.m_bookingID = b.m_bookingID
                            and t.m_tripID = b.m_tripID
                            and t.m_destinationPortID = d.m_seaportid
                            and v.m_vesselID = t.m_vesselID
                            and s.m_seaPortID = t.m_seaPortID
                            and h.m_passportID = p.m_passportID
                            and ta.m_travelAgentId = b.m_travelAgentId
                            and p.m_passangerID = ?m_passangerID";
            return sql;
        }

        public static string ReportKIMSPassengerByID()
        {
            string sql = @"select ta.m_name as travelagentname,
                            h.m_name as passname,
                            h.m_passportNo as passportcd,
                            h.m_officeissue as passofficeissue,
                            h.m_issueDate as passissuedate,
                            h.m_expiredate as passexpireddate,
                            h.m_country as countryname,
                            h.m_birthDate as passdateofbirth,
                            h.m_birthPlace as passplaceofbirth,
                            m_vesselName as vesselcd,
                            h.m_gender as passgender
                            from passanger p, booking b, trip t, vessel v, seaport s, passport h,
                            travel_agent ta, seaport d 
                            where
                            p.m_bookingID = b.m_bookingID
                            and t.m_tripID = b.m_tripID
                            and t.m_destinationPortID = d.m_seaportid
                            and v.m_vesselID = t.m_vesselID
                            and s.m_seaPortID = t.m_seaPortID
                            and h.m_passportID = p.m_passportID
                            and ta.m_travelAgentId = b.m_travelAgentId
                            and p.m_passangerID = ?m_passangerID";
            return sql;
        }
        public static string ReportBooking()
        {
            string sql = @"SELECT
                        *,
                        (select count(*) from passanger p where p.m_bookingID = b.m_bookingID ) as passangerCount
                        FROM booking b, trip t, vessel v, seaport s, travel_agent ta
                         where t.m_tripID = b.m_tripID
                        and v.m_vesselID = t.m_vesselID
                        and s.m_seaPortID = t.m_seaPortID
                        and ta.m_travelAgentId = b.m_travelAgentId 
                        and t.m_departureDate between ?fromdate and ?todate ";
            return sql;
        }
        public static string ReportManifest()
        {
            return @"SELECT * FROM trip t, vessel v, seaport s , booking b, passanger p, travel_agent ta, seaport d 
                    where b.m_tripID = ?m_tripID
                    and s.m_seaportid = t.m_seaportid
                    and v.m_vesselid = t.m_vesselid
                    and t.m_tripID = b.m_tripID
                    and p.m_bookingID=b.m_bookingID
                    and ta.m_travelAgentId = p.m_travelAgentId
                    and t.m_destinationPortID = d.m_seaportid
                    and p.m_passengerStatus = ?m_passengerStatus";
        }
        public static string ReportManifestHeader()
        {
            return @"SELECT t.m_tripcode code, t.m_departuretime deptime, v.m_vesselname vessel,
s.m_seaportName depart,d.m_seaportName arrive,
ta.m_name travelagent FROM trip t, vessel v, seaport s , seaport d, travel_agent ta
                    where t.m_tripID = ?m_tripID
                    and s.m_seaportid = t.m_seaportid
                    and v.m_vesselid = t.m_vesselid
                    and t.m_destinationPortID = d.m_seaportid
                    and ta.m_travelAgentId = ?m_travelAgentId";
        }


        public static string SelectNewBooking()
        {
            return @"SELECT * FROM booking b where m_tripID = ?m_tripid";
        }
        public static string UpdateTripByPassengerID()
        {
            return @"update passanger set m_bookingID= ?m_bookingID where m_passangerID=?m_passangerID";
        }
        public static string ReportTicket()
        {
            string sql = @"SELECT
                        *
                        FROM booking b, trip t, vessel v, seaport s, travel_agent ta,
                        passanger p
                         where t.m_tripID = b.m_tripID
                        and v.m_vesselID = t.m_vesselID
                        and s.m_seaPortID = t.m_seaPortID
                        and ta.m_travelAgentId = b.m_travelAgentId
                        and p.m_bookingID = b.m_bookingID
                        and t.m_departureDate between ?fromdate and ?todate ";
            return sql;
        }
        public static string ReportPassenger()
        {
            string sql = @"SELECT
                        *
                        FROM booking b, trip t, vessel v, seaport s, travel_agent ta,
                        passanger p
                         where t.m_tripID = b.m_tripID
                        and v.m_vesselID = t.m_vesselID
                        and s.m_seaPortID = t.m_seaPortID
                        and ta.m_travelAgentId = b.m_travelAgentId
                        and p.m_bookingID = b.m_bookingID
                        and t.m_departureDate between ?fromdate and ?todate ";
            return sql;
        }
    }
}
